﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Collections;


namespace CP2011_Assignment
{
    /// <summary>
    /// Back end, connect to DB, execute SQL
    /// </summary>
    class InventorySystem : Form, IDataController
    {
        private SqlConnection connection1 = new SqlConnection(Properties.Settings.Default.AssignmentDBConnectionString1);
            
        
        public void addToDatabase(Object inventory)
        {

            Inventory inv = (Inventory)inventory;
            String name = inv.getInvName();
            String desc = inv.getDesc();
            int qtyPerMonth = inv.getQtyPerMonth();
            int suppNum = inv.getSuppNum();
            int qtyOnHand = inv.getQtyOnHand();
                                
            ///SQL QUERY

            try
            {
                String query = "INSERT into Inventory (Inv_Name, Inv_Desc, Inv_QtyPerMonth, Inv_Supplier) values (@name, @desc, @qtyPerMonth, @supplierNum)";
                SqlCommand insertCommand = new SqlCommand(query, connection1);

                ///CONVERT VARIABLE INTO SQL PARAMETERS
                insertCommand.Parameters.AddWithValue("@name", name);
                insertCommand.Parameters.AddWithValue("@desc", desc);
                insertCommand.Parameters.AddWithValue("@qtyPerMonth", qtyPerMonth);
                insertCommand.Parameters.AddWithValue("@supplierNum", suppNum);


                if (connection1 == null)
                {
                    Console.WriteLine("no database!");
                }
                connection1.Open();
                insertCommand.ExecuteNonQuery(); ///EXECUTE SQL QUERY
                connection1.Close();

            }
            catch(SqlException ex)
            {
                Console.WriteLine("error occured");
            }

            
           Console.WriteLine("record inserted!");  

        }
        public void deleteRecord(Object ts)
        {
            TempStorage tempData = (TempStorage)ts;
            int invId = tempData.getValue1();

            ///checking whether the ID is in the database or not 
            try
            {
                String checkQuery = "SELECT Inv_ID FROM Inventory WHERE Inv_ID = " + invId;
                SqlCommand checkCommand = new SqlCommand(checkQuery, connection1);
                connection1.Open();
                SqlDataReader reader = checkCommand.ExecuteReader();

                ///if id exist, delete record 
                if (reader.Read())
                {
                    connection1.Close();
                    connection1.Open();
                    String query = "DELETE Inventory WHERE Inv_ID = " + invId;
                    SqlCommand insertCommand = new SqlCommand(query, connection1);
                    insertCommand.ExecuteNonQuery();
                    connection1.Close();
                    Console.WriteLine("success");
                }

                else
                {
                    Console.WriteLine("no such ID found!");
                    connection1.Close();
                }
            }
            catch(SqlException ex)
            {

            } 
            
        }
        public void editRecord(String query, int invId)
        {
            /*TempStorage tempData = (TempStorage)ts;
            int invId = tempData.getValue1();
            int invNewQty = tempData.getValue2();*/
           
           ///checking whether the ID is in the database or not 
            try
            {
                String checkQuery = "SELECT Inv_ID FROM Inventory WHERE Inv_ID = " + invId;
                SqlCommand checkCommand = new SqlCommand(checkQuery, connection1);
                connection1.Open();
                SqlDataReader reader = checkCommand.ExecuteReader();

                ///if id exist, update value 
                if (reader.Read())
                {
                    connection1.Close();
                    connection1.Open();
                    ///String query = "UPDATE Inventory SET Inv_QtyOnHand =" + invNewQty + "WHERE Inv_ID = " + invId;
                    SqlCommand insertCommand = new SqlCommand(query, connection1);
                    insertCommand.ExecuteNonQuery();
                    connection1.Close();
                }

                else
                {
                    MessageBox.Show("No such ID found!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    connection1.Close();
                }
            }
            catch(SqlException ex)
            {
            } 
            
        }

        public void consumeItem()
        {
        }

        public ArrayList getRecord(String getQuery)
        {
            ArrayList record = new ArrayList();

            try
            {
                SqlCommand getInv = connection1.CreateCommand();
                getInv.CommandText = getQuery;
                connection1.Open();
                SqlDataReader reader = getInv.ExecuteReader();
                while (reader.Read())
                {
                    record.Add(reader.GetString(0));
                }
            }
            catch(SqlException ex)
            {
            } 
            
            return record;
        }

        public void checkRecord(String checkQuery)
        {
            try
            {
                SqlCommand checkInv = connection1.CreateCommand();
                checkInv.CommandText = checkQuery;
                connection1.Open();
                SqlDataReader reader = checkInv.ExecuteReader();
                /// if (reader.Read()) ;
                ArrayList invId = new ArrayList();
                while (reader.Read())
                {
                    invId.Add(reader.GetInt32(0));
                }
                for (int i = 0; i < invId.Count; i++)
                {
                    int id = (int)invId[i];
                    MessageBox.Show("Warning! Inventory with ID: " + id + " is low", "Low Inventory!", MessageBoxButtons.OK, MessageBoxIcon.Warning);

                }
            }
            catch(SqlException ex)
            {

            }
            
        }
        
       
    }
}
